package br.skytef.stmanager.daos;
/**
 * 
 * @author Ivam Henrique Marques dos Santos
 *         email: ivam.santos@skytef.com.br
 *         skype: ivam.santos.skytef
 *         telefone: +55 11 96214-7543
 *         
 * @author Nathália Perez de Oliveira
 * 		   email : nathalia.perez@skytef.com.br   
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.skytef.stmanager.entities.TarefaEntity;
import br.skytef.stmanager.utils.Pool;
/**
 * 
 * Class DAO NovaTarefaDAO
 */
public class TarefaDAO {
	/**
	 * Variável para conexão, obtido através de um Pool(br.skytef.stmanager.utils.Pool)
	 */
	
	private static String erro;
	public static String getErro() {return erro;}
	
	private static Connection conexao = null;	
	/**
	 * Inserir nova tarefa/registro
	 * @param novaTarefa
	 */

	public boolean inserir(TarefaEntity tarefa){
		try {
			conexao = Pool.getConnection();

			String query = "INSERT INTO tarefa(id,descricao, status, tipoCliente, tipoSuporte, forma, dataInclusao, motivoSuporte_id,contato_id,suberro_id) "
					+ "VALUES (?,?,?,?,?,?,?,?,?,?);";
			PreparedStatement preparedStmt = conexao.prepareStatement(query);
			preparedStmt.setLong     		(1, tarefa.getId());
			preparedStmt.setString 			(2, tarefa.getDescricao());
			preparedStmt.setString			(3, tarefa.getStatus());
			preparedStmt.setString			(4, tarefa.getTipoCliente());
			preparedStmt.setString 			(5, tarefa.getTipoSuporte());
			preparedStmt.setString 			(6, tarefa.getForma());
			preparedStmt.setTimestamp		(7, tarefa.getDataInclusao());
			preparedStmt.setInt				(8, tarefa.getMotivoSuporte_id());
			preparedStmt.setInt				(9, tarefa.getContato_id());
			preparedStmt.setInt				(10,tarefa.getSuberro_id());
			preparedStmt.execute();						
			conexao.close();
			return true;

		} catch (SQLException e) {
			System.out.println("SQL erro: "+ e);
			erro=e.toString();
			return false;
		}catch (ClassCastException e) {
			System.out.println("Classe erro: "+ e);
			erro=e.toString();
			return false;
		} catch (Exception e) {
			System.out.println("Geral erro: "+ e);
			erro=e.toString();
			return false;
		}
	}	
	public TarefaEntity buscar(long id){
		TarefaEntity tarefa = new TarefaEntity();
		try {
			conexao = Pool.getConnection();
			Statement st = conexao.createStatement();
			ResultSet rs = st.executeQuery("SELECT * FROM tarefa WHERE id = "+ id +";");
			if (rs.next())
				do {    
					tarefa.setId(rs.getLong("tarefa_id"));
					tarefa.setContato(rs.getString("contato"));
					tarefa.setDescricao(rs.getString("descricao"));
					tarefa.setStatus(rs.getString("status"));
					tarefa.setTipoCliente(rs.getString("tipoCliente"));
					tarefa.setTipoSuporte(rs.getString("tipoSuporte"));
					tarefa.setForma(rs.getString("forma"));
					tarefa.setDataInclusao(rs.getTimestamp("datainclusao"));
					tarefa.setMotivoSuporte_id(rs.getInt("motivoSuporte_id"));
				}while (rs.next());
			conexao.close();			

		}catch (SQLException e) {
			System.out.println("SQL erro: "+ e);
		}
		return tarefa;
	}
	
	
	public List <TarefaEntity> listar(int idCliente,String idUsuario,String Numtarefa){
		List <TarefaEntity> tarefas = null;
		TarefaEntity tarefa;
		try {
			conexao = Pool.getConnection();
			Statement st = conexao.createStatement();
			ResultSet rs = st.executeQuery("SELECT ut.tarefa_id,tf.descricao,tf.status,tf.tipoCliente,tf.tipoSuporte,tf.forma,us.nome,cl.cnpj FROM utc as ut  "
											+"INNER JOIN tarefa  as tf ON  tf.id = ut.tarefa_id "
											+"INNER JOIN usuario as us ON  ut.usuario_id= us.id "
											+"INNER JOIN cliente as cl ON  cl.idCliente = ut.cliente_idCliente WHERE  ut.cliente_idCliente = "+ idCliente +" OR ut.usuario_id = " + idUsuario + " OR ut.tarefa_id = " + Numtarefa  + ";");
			if (rs.next()) {   
				tarefas = new ArrayList<TarefaEntity>();
				do {                      
					tarefa = new TarefaEntity();
					tarefa.setId(rs.getLong("tarefa_id"));
					tarefa.setDescricao(rs.getString("descricao"));
					tarefa.setStatus(rs.getString("status"));
					tarefa.setTipoCliente(rs.getString("tipoCliente"));
					tarefa.setTipoSuporte(rs.getString("tipoSuporte"));
					tarefa.setForma(rs.getString("forma"));
					tarefa.setNome(rs.getString("nome"));
					tarefa.setCnpj(rs.getLong("cnpj"));
					tarefas.add(tarefa);
				} while (rs.next());
			}
			conexao.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: "+ e);
		} catch (Exception e) {
			System.out.println("Geral erro: "+ e);
		}
		return tarefas;		
	}
}

